﻿using Blog190311.Models;
using Blog190311.Models.DBModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Blog190311.Areas.Back.Models.DAL
{
    public class GalleryService
    {
        /// <summary>
        /// 多条件分页查询
        /// </summary>
        /// <param name="limit"></param>
        /// <param name="page"></param>
        /// <param name="Title"></param>
        /// <param name="ShowType"></param>
        /// <param name="IsPub"></param>
        /// <param name="Tage"></param>
        /// <returns></returns>
        public List<Article> GetBypage(int limit, int page, string Title, string ShowType, string IsPub, string Tage)
        {
            List<Article> lists = new List<Article>();
            string sqlWhere = "";
            if (!string.IsNullOrWhiteSpace(Title))
            {
                sqlWhere += string.Format(" and Title like'%{0}%'", Title);
            }
            if (!string.IsNullOrWhiteSpace(ShowType))
            {
                sqlWhere += string.Format(" and  ShowType like'%{0}%'", ShowType);
            }
            if (!string.IsNullOrWhiteSpace(IsPub))
            {
                sqlWhere += string.Format(" and IsPub like'%{0}%'", IsPub);
            }
            if (!string.IsNullOrWhiteSpace(Tage))
            {
                sqlWhere += string.Format(" and Tage like'%{0}%'", Tage);
            }
            string sqlStr = string.Format("select top({0}) * from Articles where ShowType='相册' and Id not in(select top({1}) Id from Articles where ShowType='相册')", limit, limit * (page - 1));
            sqlStr = sqlStr + sqlWhere;
            SqlDataReader dr = DBHelper.ExecuteReader(sqlStr);
            while (dr.Read())
            {
                lists.Add(new Article()
                {
                    Id = Convert.ToInt32(dr["id"]),
                    Title = dr["Title"].ToString(),
                    ReadTimes = Convert.ToInt32(dr["ReadTimes"]),
                    Tags = dr["Tags"].ToString(),
                    SimpleInfo = dr["SimpleInfo"].ToString(),
                    Cont = dr["Cont"].ToString(),
                    MainPicUrl = dr["MainPicUrl"].ToString(),
                    Source = dr["Source"].ToString(),
                    Type = dr["Type"].ToString(),
                    IsPub = dr["IsPub"].ToString(),
                    CreateTime = dr["CreateTime"].ToString(),
                    PubTime = dr["PubTime"].ToString(),
                    ShowType = dr["ShowType"].ToString()

                });
            }
            return lists;
        }

        /// <summary>
        /// 获取满足条件的数据条数
        /// </summary>
        /// <param name="Title"></param>
        /// <param name="ShowType"></param>
        /// <param name="IsPub"></param>
        /// <param name="Tage"></param>
        /// <returns></returns>
        public int GetCount(string Title, string ShowType, string IsPub, string Tage)
        {
            string sqlWhere = "";
            if (!string.IsNullOrWhiteSpace(Title))
            {
                sqlWhere += string.Format(" and Title like'%{0}%'", Title);
            }
            if (!string.IsNullOrWhiteSpace(ShowType))
            {
                sqlWhere += string.Format(" and  ShowType like'%{0}%'", ShowType);
            }
            if (!string.IsNullOrWhiteSpace(IsPub))
            {
                sqlWhere += string.Format(" and IsPub like'%{0}%'", IsPub);
            }
            if (!string.IsNullOrWhiteSpace(Tage))
            {
                sqlWhere += string.Format(" and Tage like'%{0}%'", Tage);
            }
            string sqlStr = "select count(*) from Articles where ShowType='相册' ";
            sqlStr = sqlStr + sqlWhere;
            object re = DBHelper.ExecuteScalar(sqlStr);
            if (re != null)
            {
                return Convert.ToInt32(re);
            }
            return 0;
        }
        
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public int BaoCun(Article article)
        {
            string sqlr = string.Format("insert into Articles (Title,ReadTimes,Tags,SimpleInfo,Cont,MainPicUrl,Source,Type,CreateTime,PubTime,IsPub,ShowType)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')", article.Title, article.ReadTimes, article.Tags, article.SimpleInfo, article.Cont, article.MainPicUrl, article.Source, article.Type, article.CreateTime, article.PubTime, article.IsPub, article.ShowType);
            int num = DBHelper.ExecuteNonQuery(sqlr);
            return num;
        }
        
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public int XiuGai(Article article)
        {
            string sqlr = string.Format("update Articles set Title ='{0}',ReadTimes='{1}',Tags='{2}',SimpleInfo='{3}',Cont='{4}',MainPicUrl='{5}',Source='{6}',Type='{7}',CreateTime='{8}',PubTime='{9}',IsPub='{10}',ShowType='{11}' where Id='{12}'", article.Title, article.ReadTimes, article.Tags, article.SimpleInfo, article.Cont, article.MainPicUrl, article.Source, article.Type, article.CreateTime, article.PubTime, article.IsPub, article.ShowType, article.Id);

            int num = DBHelper.ExecuteNonQuery(sqlr);
            return num;
        }

        /// <summary>
        /// 通过Id获取相册单条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article GetId(int id)
        {
            string sqlr = string.Format("select * from Articles where id={0}", id);
            SqlDataReader dr = DBHelper.ExecuteReader(sqlr);//获取多个值的方法
            if (dr.Read())//read方法移动下一条记录 有记录true无记录false sdr是一个读取器
            {
                Article art = new Article()
                {
                    Id = Convert.ToInt32(dr["id"]),
                    Title = dr["Title"].ToString(),
                    ReadTimes = Convert.ToInt32(dr["ReadTimes"]),
                    Tags = dr["Tags"].ToString(),
                    SimpleInfo = dr["SimpleInfo"].ToString(),
                    Cont = dr["Cont"].ToString(),
                    MainPicUrl = dr["MainPicUrl"].ToString(),
                    Source = dr["Source"].ToString(),
                    Type = dr["Type"].ToString(),
                    IsPub = dr["IsPub"].ToString(),
                    CreateTime = dr["CreateTime"].ToString(),
                    PubTime = dr["PubTime"].ToString(),
                    ShowType = dr["ShowType"].ToString()
                };
                return art;
            }
            return null;
        }
        
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int ShanChu(int id)
        {
            string sqlr = string.Format("delete from Articles where id='{0}'", id);
            int num = DBHelper.ExecuteNonQuery(sqlr);
            return num;
        }

        /// <summary>
        /// 发布
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int FaBu(int id)
        {
            string sqlr = string.Format("update Articles set IsPub='是' where id='{0}'", id);
            int num = DBHelper.ExecuteNonQuery(sqlr);
            return num;
        }
    }
}